Working with TAKE, DROP, CHOOSECOLS, and CHOOSEROWS in Excel

Course: Advanced Excel

Topic: Working with TAKE, DROP, CHOOSECOLS, and CHOOSEROWS

Course Link: Advanced Excel Course – JustAcademy

Demo Link: Register for a Course Demo

Website: Visit JustAcademy

1. Introduction

Modern Excel includes dynamic array functions that allow users to extract, remove, and rearrange parts of a dataset without manually copying and pasting cells. Four useful functions for this purpose are TAKE, DROP, CHOOSECOLS, and CHOOSEROWS.

These functions are especially helpful when working with large reports, sales records, employee databases, financial summaries, dashboards, and data preparation tasks. They return results dynamically, meaning the output can update automatically when the source data or formulas change.

• TAKE: Returns a specified number of rows or columns from the beginning or end of an array.
• DROP: Removes a specified number of rows or columns from the beginning or end of an array.
• CHOOSECOLS: Returns selected columns from an array by column position.
• CHOOSEROWS: Returns selected rows from an array by row position.

2. Learning Objectives

After studying this topic, you should be able to:

• Understand the purpose and syntax of TAKE, DROP, CHOOSECOLS, and CHOOSEROWS.
• Extract the first or last records from a dataset.
• Remove unnecessary rows and columns from a report.
• Select specific columns or rows using their positions.
• Combine these functions with SORT, FILTER, and other dynamic array functions.
• Build reusable reports without manually rearranging source data.
• Identify and troubleshoot common formula errors.

3. Understanding Dynamic Arrays

A dynamic array formula can return multiple values into neighboring cells automatically. This output is called a spilled array.

For example, if a formula returns five rows and three columns, Excel can display the complete result in a five-row-by-three-column area, provided the destination cells are empty.

TAKE, DROP, CHOOSECOLS, and CHOOSEROWS are designed to work with arrays, including ranges, Excel Tables, and results returned by other dynamic array formulas.

Example Dataset

Assume the dataset, including its header row, is stored in cells A1:E7. The formulas in the following sections use this range.

4. TAKE Function

4.1 What Is TAKE?

The TAKE function returns a specified number of consecutive rows or columns from an array. It can extract data from either the beginning or the end of the selected array.

Use TAKE when you need the first few records, the latest records at the bottom of a list, or a limited section of a larger dataset.

4.2 Syntax

=TAKE(array, rows, [columns])

4.3 Extract the First Three Rows

=TAKE(A1:E7,3)

This formula returns the first three rows of the selected array, including the header row if the range starts with headers.

To return the header and the first two employee records, this formula is suitable because the first three rows of A1:E7 contain the header and two data rows.

4.4 Extract the Last Two Employee Records

=TAKE(A2:E7,-2)

This formula returns the last two rows from the employee data, excluding the header because the source begins at A2. In the example, it returns employee IDs 105 and 106.

4.5 Return the First Three Columns

=TAKE(A1:E7,,3)

Leaving the rows argument empty allows the formula to return all rows while limiting the output to the first three columns.

4.6 Return the Last Two Columns

=TAKE(A1:E7,,-2)

This formula returns all rows from the source but selects the last two columns: Salary and Location.

4.7 Return a Specific Number of Rows and Columns

=TAKE(A1:E7,4,3)

This formula returns the first four rows and first three columns, producing a compact table containing Employee ID, Employee Name, and Department.

4.8 Practical Applications of TAKE

• Display the first five records in a summary report.
• Show the latest transactions entered at the bottom of a log.
• Limit a large data range to the columns needed for a dashboard.
• Create a preview of a dataset for quality checking.
• Extract a fixed-size section from the result of another formula.

5. DROP Function

5.1 What Is DROP?

The DROP function removes a specified number of rows or columns from an array and returns the remaining data. Unlike TAKE, which returns a selected portion, DROP excludes the specified portion.

Use DROP when you want to remove headers from a range, discard initial records, or remove unnecessary columns from a report.

5.2 Syntax

=DROP(array, rows, [columns])

5.3 Remove the Header Row

=DROP(A1:E7,1)

This formula removes the first row of the source array. The result contains only employee records, without the column headings.

5.4 Remove the Last Two Records

=DROP(A2:E7,-2)

This formula removes the final two rows from the employee data. The remaining output contains the first four employee records.

5.5 Remove the First Two Columns

=DROP(A1:E7,,2)

This formula removes Employee ID and Employee Name, leaving Department, Salary, and Location.

5.6 Remove the Last Column

=DROP(A1:E7,,-1)

This formula removes the rightmost column, Location, while retaining the other columns.

5.7 Remove Both Rows and Columns

=DROP(A1:E7,1,2)

This formula removes the first row and the first two columns. The output contains the employee data without the header, Employee ID, or Employee Name.

5.8 Practical Applications of DROP

• Remove title rows from imported reports.
• Exclude unnecessary columns from a dataset.
• Remove initial or final records when preparing data for analysis.
• Clean up source arrays before passing them into other formulas.
• Create a data-only output without modifying the original range.

6. CHOOSECOLS Function

6.1 What Is CHOOSECOLS?

CHOOSECOLS returns selected columns from an array based on their positions. You can choose one column or multiple columns, and the output follows the order of the column numbers specified in the formula.

This function is particularly useful when a report needs only a few fields from a wide table.

6.2 Syntax

=CHOOSECOLS(array, col_num1, [col_num2], ...)

Column positions are counted from the left side of the supplied array. Positive numbers identify columns from left to right, while negative numbers can identify columns from right to left.

6.3 Select Employee Name and Salary

=CHOOSECOLS(A1:E7,2,4)

This formula returns the second and fourth columns of A1:E7: Employee Name and Salary. The other columns are excluded from the output.

6.4 Select Columns in a Different Order

=CHOOSECOLS(A1:E7,4,2,5)

This formula returns Salary, Employee Name, and Location in that order. The order of the output is determined by the order of the column numbers in the formula.

6.5 Select the Last Column

=CHOOSECOLS(A1:E7,-1)

This formula returns the final column of the source array, which is Location in the example dataset.

6.6 Select Non-Adjacent Columns

=CHOOSECOLS(A1:E7,1,3,5)

This formula returns Employee ID, Department, and Location. It is useful when the required fields are separated by columns that are not needed in the final report.

6.7 Reorder Columns for a Report

Suppose a manager wants a report with the employee's name first, department second, and salary third. The source dataset stores those fields in positions 2, 3, and 4.

=CHOOSECOLS(A1:E7,2,3,4)

The formula creates the desired three-column output without rearranging the original dataset.

6.8 Practical Applications of CHOOSECOLS

• Create simplified reports from wide datasets.
• Rearrange fields into a presentation-friendly order.
• Prepare selected fields for charts and dashboards.
• Extract specific columns from imported data.
• Combine selected columns with FILTER or SORT for dynamic reports.

7. CHOOSEROWS Function

7.1 What Is CHOOSEROWS?

CHOOSEROWS returns specific rows from an array based on their positions. You can select one or multiple rows, including rows that are not next to one another.

Use this function when you need particular records from a dataset and already know their row positions.

7.2 Syntax

=CHOOSEROWS(array, row_num1, [row_num2], ...)

Row positions are counted from the top of the supplied array. Positive numbers count from the first row, while negative numbers count backward from the last row.

7.3 Select the First Data Row

=CHOOSEROWS(A2:E7,1)

This formula returns the first employee record in the selected data range, which belongs to Aarav.

7.4 Select the First and Third Data Rows

=CHOOSEROWS(A2:E7,1,3)

This formula returns the first and third rows of the source range, corresponding to Aarav and Rohan. It skips the second row.

7.5 Select the Last Two Data Rows

=CHOOSEROWS(A2:E7,-2,-1)

This formula returns the last two rows of the employee data, corresponding to Vikram and Ananya. Negative row numbers count backward from the bottom of the supplied array.

7.6 Return Rows in a Custom Order

=CHOOSEROWS(A2:E7,4,1,3)

This formula returns the fourth, first, and third rows of the source array in that specified order. The result contains Neha, Aarav, and Rohan.

7.7 Select Rows from a Formula Result

=CHOOSEROWS(SORT(A2:E7,4,-1),1,2)

This formula first sorts the employee records by the fourth column, Salary, in descending order. CHOOSEROWS then returns the first two rows of that sorted result, showing the two highest-paid employees in the sample data.

This pattern demonstrates how CHOOSEROWS can work with other dynamic array functions. The row selection is based on the order of the formula result, not necessarily the original worksheet row numbers.

7.8 Practical Applications of CHOOSEROWS

• Extract specific records by known position.
• Return selected rows from a sorted report.
• Display sample records for review.
• Reorder chosen records for a summary.
• Combine with FILTER and SORT to create focused reports.

8. Comparison of the Four Functions

9. Combining TAKE, DROP, CHOOSECOLS, and CHOOSEROWS

The four functions become more powerful when combined. You can first select a section of a dataset, remove unnecessary records, and then return only the required fields.

9.1 Take the First Three Employee Records and Show Name and Salary

=CHOOSECOLS(TAKE(A2:E7,3),2,4)

TAKE first returns the first three employee records. CHOOSECOLS then returns only Employee Name and Salary from that result.

9.2 Remove the Header and Select Name and Department

=CHOOSECOLS(DROP(A1:E7,1),2,3)

DROP removes the header row, and CHOOSECOLS returns the Employee Name and Department columns from the remaining data.

9.3 Return the Last Three Records with Selected Columns

=CHOOSECOLS(TAKE(A2:E7,-3),2,4,5)

This formula selects the last three employee records and displays Employee Name, Salary, and Location.

9.4 Select Specific Rows and Then Choose Columns

=CHOOSECOLS(CHOOSEROWS(A2:E7,1,3,5),2,4)

CHOOSEROWS selects the first, third, and fifth employee records. CHOOSECOLS then returns only the Employee Name and Salary fields for those records.

9.5 Create a Compact Report from a Sorted Dataset

=CHOOSECOLS(TAKE(SORT(A2:E7,4,-1),3),2,3,4)

This formula sorts the employee data by Salary in descending order, takes the top three records, and displays Employee Name, Department, and Salary.

It is a useful pattern for creating a top-three salary report that updates when the source data changes.

10. Using These Functions with Excel Tables

For ongoing reports, converting a dataset into an Excel Table can make formulas easier to maintain. Select the dataset and choose Insert > Table, or use Ctrl+T. Give the table a meaningful name, such as EmployeeData.

If the table has columns named Employee ID, Employee Name, Department, Salary, and Location, a formula such as the following can return the first three employee records:

=TAKE(EmployeeData,3)

To select the Employee Name and Salary fields from the table, use the appropriate column positions:

=CHOOSECOLS(EmployeeData,2,4)

Structured table references generally expand as table records are added or removed. This makes them useful for regularly updated reports. Remember that the functions select columns by position, so changing the order of table columns may change the result.

11. Common Errors and Troubleshooting

12. Important Best Practices

• Check your source range: Confirm that the array contains all the rows and columns you need.
• Handle headers deliberately: Include the header row when it should appear in the output; use DROP when it should be removed.
• Understand positive and negative numbers: TAKE and DROP use signs to specify the beginning or end. CHOOSECOLS and CHOOSEROWS use positions to identify individual columns and rows.
• Count within the array: Position numbers are relative to the first row or column of the array argument.
• Leave room for spilled results: Ensure that the destination area is not blocked by existing content.
• Use Excel Tables for changing data: Structured references can reduce the need to update ranges manually.
• Combine functions carefully: Nested formulas are powerful, but break complicated formulas into smaller steps when troubleshooting.
• Do not confuse position with criteria: CHOOSEROWS selects by row number, not by matching a value such as a department name. Use FILTER when you need criteria-based selection.
• Test edge cases: Check what happens when the source contains very few records or when requested positions exceed the array dimensions.

13. Hands-On Practice Exercises

Use the sample employee dataset in A1:E7 to complete these exercises.

1. Use TAKE to display the header and the first three employee records.
2. Use TAKE to return the last two employee records without the header.
3. Use DROP to remove the header row from the dataset.
4. Use DROP to remove the first two columns from the full dataset.
5. Use CHOOSECOLS to display Employee Name, Salary, and Location.
6. Use CHOOSECOLS to display Location followed by Employee Name.
7. Use CHOOSEROWS to return the first, third, and sixth employee records.
8. Use CHOOSEROWS to return the last two employee records.
9. Combine TAKE and CHOOSECOLS to show the first four employee names and salaries.
10. Combine SORT, TAKE, and CHOOSECOLS to create a report of the top three salaries.

Suggested Answers

14. Mini Project: Dynamic Employee Summary Report

Project Objective

Create a compact employee report that displays the three highest-paid employees and includes only their names, departments, and salaries.

Steps

1. Enter the employee dataset with clear column headings.
2. Ensure the Salary column contains numeric values.
3. Use SORT to arrange employee records by Salary in descending order.
4. Use TAKE to select the first three records from the sorted result.
5. Use CHOOSECOLS to retain Employee Name, Department, and Salary.
6. Place the formula in an empty area of the worksheet.
7. Format the output as a professional report and test it after changing a salary value.

Final Formula

=CHOOSECOLS(TAKE(SORT(A2:E7,4,-1),3),2,3,4)

Expected result: The formula displays the three highest-paid employees, ordered from highest salary to lowest, with only their names, departments, and salaries. The result recalculates when the source data changes.

15. Interview Questions and Answers

Q1. What is the purpose of the TAKE function?

Answer: TAKE returns a specified number of rows or columns from the beginning or end of an array. Positive values select from the beginning, while negative values select from the end.

Q2. How is DROP different from TAKE?

Answer: TAKE returns the requested portion of an array, while DROP removes the requested rows or columns and returns what remains.

Q3. What does CHOOSECOLS do?

Answer: CHOOSECOLS returns specific columns from an array according to their positions. It can also change the output column order by specifying the positions in a different order.

Q4. What is the purpose of CHOOSEROWS?

Answer: CHOOSEROWS returns selected rows from an array based on row positions. It supports selecting non-adjacent rows and returning them in a specified order.

Q5. Can these functions be combined?

Answer: Yes. For example, TAKE can limit the number of records, and CHOOSECOLS can then select the fields needed for the final report.

Q6. How do negative numbers work in TAKE and DROP?

Answer: Negative row counts refer to the end of the array, and negative column counts refer to the right side. In TAKE, they select from the end or right; in DROP, they remove from the end or right.

Q7. How do negative positions work in CHOOSECOLS and CHOOSEROWS?

Answer: Negative positions count backward from the end of the supplied array. For example, -1 refers to the last row in CHOOSEROWS or the last column in CHOOSECOLS.

Q8. What causes a #SPILL! error?

Answer: A #SPILL! error often occurs when cells required for the dynamic array output are not empty or when Excel cannot place the complete result. Clear the blocking cells and check the output area.

Q9. When should FILTER be used instead of CHOOSEROWS?

Answer: Use CHOOSEROWS when you know which row positions to return. Use FILTER when records should be selected based on conditions, such as employees belonging to the IT department.

Q10. What is one advantage of using these functions in reports?

Answer: They reduce manual copying and rearranging, produce repeatable formulas, and can update dynamically when source data changes.

16. Quick Revision Checklist

• TAKE returns a specified number of rows or columns.
• DROP removes specified rows or columns.
• CHOOSECOLS selects columns by position.
• CHOOSEROWS selects rows by position.
• Positive and negative values can control selection from opposite ends of an array.
• Position numbers are relative to the supplied array, not the worksheet's absolute row or column numbers.
• These functions can be nested with SORT, FILTER, and other dynamic array functions.
• Dynamic array outputs need enough unobstructed space to spill.
• Excel versions without support for these functions may not calculate the formulas.

17. Conclusion

TAKE, DROP, CHOOSECOLS, and CHOOSEROWS are valuable tools for efficient data preparation in Advanced Excel. TAKE extracts a portion of an array, DROP removes unwanted portions, CHOOSECOLS selects specific fields, and CHOOSEROWS selects specific records. Together, these functions help create compact reports, reorganize data, and automate repetitive spreadsheet tasks.

Practice using the functions individually before combining them into nested formulas. Once you understand their arguments and position rules, you can build flexible reports that update automatically and reduce manual data handling.

Explore More:

• Explore the Advanced Excel Course
• Register for a Course Demo
• Visit the JustAcademy Website